﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Light.Utility;
using Light.Framework.Model;
using Light.Framework.Castle;

namespace Light.Framework
{
    /// <summary>
    /// 数据表查询器
    /// </summary>
    public class DataTableMediator
    {
        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="tableOrViewName">表名或视图名</param>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null</param>
        /// <returns></returns>
        public static DataTable GetAll(string tableOrViewName, SearchArgs searchArgs, ResultOrder resultOrder)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();

            string where = searchArgs.ToSqlString();
            string order = resultOrder.ToSqlString();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                List<IDataParameter> listParams = searchArgs.GetSqlParameters(executer);
                string sql = string.Format(@"SELECT {0} FROM {1} {2} ORDER BY {3}", "*", tableOrViewName, where, order);
                SQLLoger.Write(sql, listParams.ToArray());

                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        /// <summary>
        /// 单 表或视图 分页查询，返回DataTable
        /// </summary>
        /// <param name="tableOrViewName">表名或视图名</param>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="pageArgs">分页参数，不能为null</param>
        /// <param name="resultOrder">排序参数，不能为null</param>
        /// <returns></returns>
        public static DataTable Search(string tableOrViewName, SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null && resultOrder.IsEmpty)
                throw new Exception("排序参数 ResultOrder 不能为空");
            if (pageArgs == null)
                throw new Exception("分页参数 PageArgs 不能为空");

            string whereSql = searchArgs.ToSqlString();
            string orderSql = resultOrder.ToSqlString();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                List<IDataParameter> listParams = searchArgs.GetSqlParameters(executer);

                //获取总记录条数
                string countSql = string.Format("SELECT COUNT(*) FROM {0} {1}", tableOrViewName, whereSql);
                pageArgs.TotalResults = StringHelper.ToInt(executer.ExecuteScalar(countSql, listParams.ToArray()));

                string sql = string.Empty;

                if (executer.DBMS == Database.MySQL)
                {
                    //查询结果集
                    sql = string.Format(@"
                                SELECT {0}
                                FROM {1}
                                 {2}
                                ORDER BY {3}
                                LIMIT {4},{5}", "*", tableOrViewName, whereSql, orderSql, (pageArgs.PageIndex - 1) * pageArgs.PageSize, pageArgs.PageSize);
                }
                else if (executer.DBMS == Database.SqlServer)
                {

                    //查询结果集
                    sql = string.Format(@"
                                SELECT {0}
                                FROM {1}
                                 {2}
                                ORDER BY {3}
                                OFFSET {4} ROWS
                                FETCH NEXT {5} ROWS ONLY", "*", tableOrViewName, whereSql, orderSql, (pageArgs.PageIndex - 1) * pageArgs.PageSize, pageArgs.PageSize);
                }

                SQLLoger.Write(sql, listParams.ToArray());

                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        /// <summary>
        /// 金额求和
        /// </summary>
        /// <param name="columnName">数据库列名</param>
        /// <param name="searchArgs">查询参数</param>
        /// <returns></returns>
        public static decimal Sum(string tableOrViewName, string columnName, SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string where = searchArgs.ToSqlString();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                List<IDataParameter> listParams = searchArgs.GetSqlParameters(executer);
                string sql = string.Format(@"SELECT SUM({0}) FROM {1} {2}", columnName, tableOrViewName, where);

                SQLLoger.Write(sql, listParams.ToArray());

                object result = executer.ExecuteScalar(sql, listParams.ToArray());
                return StringHelper.ToDecimal(result);
            }
        }
    }
}
